package com.bt.lms.dao.servise.impl;

import com.bt.lms.bo.EnrollmentList;
import com.bt.lms.constant.BOConstant;
import com.bt.lms.entity.EnrollmentPlan;
import com.bt.lms.entity.TeachingPlanBasicInfo;
import com.bt.lms.dao.servise.EnrollmentPlanDao;
import com.bt.lms.entity.page.Page;
import com.bt.lms.utils.ConnectUtils;

import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.UUID;

/**
 * 招生计划Dao实现
 *
 * @author 李鹏
 */
public class EnrollmentPlanDaoImpl implements EnrollmentPlanDao {

    @Override
    public int insert(String sql, EnrollmentPlan enrollmentPlan) throws Exception {

        try {
            //1.有两个表，招生计划  报名材料类型
            //维护（不是客户端直接输入的）
            enrollmentPlan.setStatus(BOConstant.ENROLLMENT_PLAN_STATUS_NOT_START);//招生计划状态(未开始)
            enrollmentPlan.setStartStatus(BOConstant.ENROLLMENT_PLAN_START_STATUS_STOP);//启用状态(暂停)
            //招生计划编号
            UUID uuid = UUID.randomUUID();
            String enpPlanCode = uuid.toString();
            enrollmentPlan.setCode(enpPlanCode);

            //2.准备动数据库（入）
            //2.1招生计划表
            String planName = enrollmentPlan.getPlanName();//名称
            String planContent = enrollmentPlan.getPlanContent();//内容
            Integer level = enrollmentPlan.getLevel();//招生层次
            Integer major = enrollmentPlan.getMajor();//招生专业
            Integer population = enrollmentPlan.getPopulation();//招生人数
            Integer tuitionStandard = enrollmentPlan.getTuitionStandard();//学费标准
            LocalDate startTime = enrollmentPlan.getStartTime();//报名开始时间
            LocalDate stopTime = enrollmentPlan.getStopTime();//报名结束时间
            Integer regMode = enrollmentPlan.getRegMode();//报名方式
            String regPlace = enrollmentPlan.getRegPlace();//报名地点
            String contact = enrollmentPlan.getContact();//联系人
            String phone = enrollmentPlan.getPhone();//电话
            String email = enrollmentPlan.getEmail();//电子邮箱
            String enpItem = enrollmentPlan.getEnpItem();//报名事项
            String graduateRelatedMatters = enrollmentPlan.getGraduateRelatedMatters();//毕/结业相关事项
            String replenishExplain = enrollmentPlan.getReplenishExplain();//补充说明
            Long teachPlanId = enrollmentPlan.getTeachPlanId();//教学计划ID

            //jdbc五步
            Connection connection = ConnectUtils.connect();
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, enpPlanCode);
            ps.setString(2, planName);
            ps.setString(3, planContent);
            ps.setInt(4, level);
            ps.setInt(5, major);
            ps.setInt(6, population);
            ps.setInt(7, tuitionStandard);
            ps.setDate(8, Date.valueOf(startTime));
            ps.setDate(9, Date.valueOf(stopTime));
            ps.setInt(10, regMode);
            ps.setString(11, regPlace);
            ps.setString(12, contact);
            ps.setString(13, phone);
            ps.setString(14, email);
            ps.setString(15, enpItem);
            ps.setString(16, graduateRelatedMatters);
            ps.setString(17, replenishExplain);
            ps.setLong(18, teachPlanId);

            ps.executeUpdate();//执行
            ResultSet generatedKeys = ps.getGeneratedKeys();
            generatedKeys.next();
            int enpId = generatedKeys.getInt(1);

            return enpId;

        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public int insertReg(String sql, EnrollmentPlan enrollmentPlan) throws Exception {

        //招生计划的报名材料类型表
        List<Integer> registrationTypes = enrollmentPlan.getRegistrationType();
        Long enpId = enrollmentPlan.getId();

        for (Integer registrationType : registrationTypes) {
            Connection connection1 = ConnectUtils.connect();
            PreparedStatement ps1 = connection1.prepareStatement(sql);

            ps1.setInt(1, registrationType);
            ps1.setLong(2, enpId);
            int i = ps1.executeUpdate();
            //关闭资源
            ps1.close();
            connection1.close();
            return i;
        }
        return 0;
    }

    @Override
    public EnrollmentPlan select(String sql, Long id) throws Exception {

        //jdbc执行sql语句，知道招生计划的id
        //jdbc五大步
        Connection connection = ConnectUtils.connect();
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setLong(1,id);
        //招生计划信息
        EnrollmentPlan enrollmentPlan = new EnrollmentPlan();
        ResultSet resultSet = ps.executeQuery();
        boolean next = resultSet.next();
        if (next) {
            //招生计划信息
            int id1 = resultSet.getInt("id");
            String code = resultSet.getString("enp_plan_code");
            String name = resultSet.getString("plan_name");
            String planContent = resultSet.getString("plan_content");
            int level = resultSet.getInt("enp_level");
            int major = resultSet.getInt("enp_major");
            int population = resultSet.getInt("enp_population");
            int tuitionStandard = resultSet.getInt("tuition_standard");
            String enpStartTime = resultSet.getString("enp_start_time");
            String enpStopTime = resultSet.getString("enp_stop_time");
            int reqMode = resultSet.getInt("req_mode");
            String enpPlace = resultSet.getString("enp_place");
            String contact = resultSet.getString("contact");
            int phone = resultSet.getInt("phone");
            String email = resultSet.getString("email");
            int registrationType = resultSet.getInt("registration_type");
            String enpItem = resultSet.getString("enp_item");
            String graduateRelatedMatters = resultSet.getString("graduate_related_matters");
            String replenishExplain = resultSet.getString("replenish_explain");
            int teachPlanId = resultSet.getInt("teach_plan_id");
            //带走
            enrollmentPlan.setCode(code);
            enrollmentPlan.setPlanName(name);
            enrollmentPlan.setPlanContent(planContent);
            enrollmentPlan.setLevel(level);
            enrollmentPlan.setMajor(major);
            enrollmentPlan.setPopulation(population);
            enrollmentPlan.setTuitionStandard(tuitionStandard);
            //String转localDate
            LocalDate localDateStart = LocalDate.parse(enpStartTime, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            enrollmentPlan.setStartTime(localDateStart);
            //String转localDate
            LocalDate localDateStop = LocalDate.parse(enpStopTime, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            enrollmentPlan.setStopTime(localDateStop);
            enrollmentPlan.setRegMode(reqMode);
            enrollmentPlan.setRegPlace(enpPlace);
            enrollmentPlan.setContact(contact);
            //int转String
            String phoneStr = Integer.toString(phone);
            enrollmentPlan.setPhone(phoneStr);
            enrollmentPlan.setEmail(email);
            enrollmentPlan.setEnpItem(enpItem);
            enrollmentPlan.setGraduateRelatedMatters(graduateRelatedMatters);
            enrollmentPlan.setReplenishExplain(replenishExplain);
        }
        return enrollmentPlan;
    }

    @Override
    public EnrollmentPlan selectRegType(String sql1, Long id) throws Exception {

        //招生计划所需报名材料类型信息
        //jdbc执行sql语句，知道招生计划的id
        Connection connection = ConnectUtils.connect();
        PreparedStatement ps = connection.prepareStatement(sql1);
        ps.setLong(1,id);
        //招生计划信息
        EnrollmentPlan enrollmentPlan = new EnrollmentPlan();
        ResultSet typesRS = ps.executeQuery();
        List<Integer> types = new ArrayList<>();
        while (typesRS.next()) {
            int type = typesRS.getInt("type");
            types.add(type);
        }
        //带走报名类型
        enrollmentPlan.setRegistrationType(types);

        return enrollmentPlan;
    }

    @Override
    public EnrollmentPlan selectTeachPlanName(String sql2, Long teachPlanId) throws Exception {

        //教学计划
        //jdbc执行sql语句，知道招生计划的id
        Connection connection = ConnectUtils.connect();
        PreparedStatement ps = connection.prepareStatement(sql2);
        ps.setLong(1,teachPlanId);
        //招生计划信息
        EnrollmentPlan enrollmentPlan = new EnrollmentPlan();
        ResultSet resultSet1 = ps.executeQuery(); //执行
        boolean next1 = resultSet1.next();
        if (next1) {
            String teachPlanName = resultSet1.getString("teach_plan_name");
            //带走
            TeachingPlanBasicInfo teachingPlanBasicInfo = new TeachingPlanBasicInfo();
            teachingPlanBasicInfo.setName(teachPlanName);
            enrollmentPlan.setTeachingPlanBasicInfo(teachingPlanBasicInfo); //互相带走

            //5.关资源
            ps.close();
            connection.close();

        }
        return enrollmentPlan;
    }

    @Override
    public boolean update(String sql, Long id, EnrollmentPlan enrollmentPlan) throws Exception {

        try {
            //1.有两个表，招生计划  报名材料类型
            //维护（不是客户端直接输入的）
            enrollmentPlan.setStatus(BOConstant.ENROLLMENT_PLAN_STATUS_NOT_START);//招生计划状态(未开始)
            enrollmentPlan.setStartStatus(BOConstant.ENROLLMENT_PLAN_START_STATUS_STOP);//启用状态(暂停)

            //2.准备动数据库（入）
            //2.1招生计划表
            //招生计划编号
            UUID uuid = UUID.randomUUID();
            String enpPlanCode = uuid.toString();
            enrollmentPlan.setCode(enpPlanCode);

            String planName = enrollmentPlan.getPlanName();//名称
            String planContent = enrollmentPlan.getPlanContent();//内容
            Integer level = enrollmentPlan.getLevel();//招生层次
            Integer major = enrollmentPlan.getMajor();//招生专业
            Integer population = enrollmentPlan.getPopulation();//招生人数
            Integer tuitionStandard = enrollmentPlan.getTuitionStandard();//学费标准
            LocalDate startTime = enrollmentPlan.getStartTime();//报名开始时间
            LocalDate stopTime = enrollmentPlan.getStopTime();//报名结束时间
            Integer regMode = enrollmentPlan.getRegMode();//报名方式
            String regPlace = enrollmentPlan.getRegPlace();//报名地点
            String contact = enrollmentPlan.getContact();//联系人
            String phone = enrollmentPlan.getPhone();//电话
            String email = enrollmentPlan.getEmail();//电子邮箱
            String enpItem = enrollmentPlan.getEnpItem();//报名事项
            String graduateRelatedMatters = enrollmentPlan.getGraduateRelatedMatters();//毕/结业相关事项
            String replenishExplain = enrollmentPlan.getReplenishExplain();//补充说明
            Long teachPlanId = enrollmentPlan.getTeachPlanId();//教学计划ID

            //jdbc五大步
            Connection connection = ConnectUtils.connect();
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, enpPlanCode);
            ps.setString(2, planName);
            ps.setString(3, planContent);
            ps.setInt(4, level);
            ps.setInt(5, major);
            ps.setInt(6, population);
            ps.setInt(7, tuitionStandard);
            ps.setDate(8, Date.valueOf(startTime));
            ps.setDate(9, Date.valueOf(stopTime));
            ps.setInt(10, regMode);
            ps.setString(11, regPlace);
            ps.setString(12, contact);
            ps.setString(13, phone);
            ps.setString(14, email);
            ps.setString(15, enpItem);
            ps.setString(16, graduateRelatedMatters);
            ps.setString(17, replenishExplain);
            ps.setLong(18,id);
            ps.setLong(19, teachPlanId);


            ps.executeUpdate();//执行
            ResultSet generatedKeys = ps.getGeneratedKeys();
            generatedKeys.next();


        } catch (Exception e) {
            throw e;
        }
        return false;
    }


    @Override
    public boolean updateReg(String sql, Long id, EnrollmentPlan enrollmentPlan) throws Exception {

        //招生计划的报名材料类型表
        List<Integer> registrationTypes = enrollmentPlan.getRegistrationType();
        for (Integer registrationType : registrationTypes) {
            //jdbc五大步
            Connection connection1 = ConnectUtils.connect();
            PreparedStatement ps1 = connection1.prepareStatement(sql);

            ps1.setInt(1, registrationType);
            ps1.setLong(2,id);
            int i = ps1.executeUpdate();
            //关闭资源
            ps1.close();
            connection1.close();
        }
        return false;
    }


    @Override
    public boolean delete(String sql, Long id) throws Exception {

        //jdbc,执行sql语句，知道插入的课程信息ID
        //jdbc五步
        Connection connection = ConnectUtils.connect();
        //4.执行sql语句
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setLong(1, id);
        //执行
        ps.executeUpdate();

        return false;
    }


    @Override
    public boolean updateStart(String sql, Long id) throws Exception {

        //jdbc,执行sql语句，知道插入的课程信息ID
        //jdbc五步
        Connection connection = ConnectUtils.connect();
        //4.执行sql语句
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setLong(1, id);
        //执行
        ps.executeUpdate();

        return false;
    }


    @Override
    public boolean updateStop(String sql, Long id) throws Exception {

        //jdbc,执行sql语句，知道插入的课程信息ID
        //jdbc五步
        Connection connection = ConnectUtils.connect();
        //4.执行sql语句
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setLong(1, id);
        //执行
        ps.executeUpdate();

        return false;
    }


    @Override
    public boolean updateFinish(String sql, Long id) throws Exception {

        //jdbc,执行sql语句，知道插入的课程信息ID
        //jdbc五步
        Connection connection = ConnectUtils.connect();
        //4.执行sql语句
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setLong(1, id);
        //执行
        ps.executeUpdate();

        return false;
    }

    @Override
    public Page<EnrollmentList> pagingConditionQuery(String sql, Page<EnrollmentList> page,Page<EnrollmentPlan> enrollmentPlanPage) {

        //分页条件模型
        ArrayList<EnrollmentPlan> enrollmentPlans = new ArrayList<>();

        //jdbc五步
        Connection connection = ConnectUtils.connect();
        //4.执行sql语句
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            Integer current = enrollmentPlanPage.getCurrent(); //当前页
            Integer size = enrollmentPlanPage.getSize(); //显示数量
            int startSize = size * (current - 1);//开始的条数
            ps.setInt(1, startSize);
            ps.setInt(2, size);
            ResultSet resultSet = ps.executeQuery();
            int i = 0;
            while (resultSet.next()) {
                long id = resultSet.getLong("id");
                String code = resultSet.getString("enp_plan_code");
                String name = resultSet.getString("plan_name");
                String planContent = resultSet.getString("plan_content");
                int level = resultSet.getInt("enp_level");
                int major = resultSet.getInt("enp_major");
                int population = resultSet.getInt("enp_population");
                int tuitionStandard = resultSet.getInt("tuition_standard");
                String startTime = resultSet.getString("enp_start_time");
                String stopTime = resultSet.getString("enp_stop_time");
                int reqMode = resultSet.getInt("req_mode");
                String enpPlace = resultSet.getString("enp_place");
                String contact = resultSet.getString("contact");
                int phone = resultSet.getInt("phone");
                String email = resultSet.getString("email");
                int registrationType = resultSet.getInt("registration_type");
                String enpItem = resultSet.getString("enp_item");
                String graduateRelatedMatters = resultSet.getString("graduate_related_matters");
                String replenishExplain = resultSet.getString("replenish_explain");

                EnrollmentPlan enrollmentPlan = new EnrollmentPlan();

                enrollmentPlan.setId(id);
                enrollmentPlan.setCode(code);
                enrollmentPlan.setPlanName(name);
                enrollmentPlan.setPlanContent(planContent);
                enrollmentPlan.setLevel(level);
                enrollmentPlan.setMajor(major);
                enrollmentPlan.setPopulation(population);
                enrollmentPlan.setTuitionStandard(tuitionStandard);
                //String转localDate
                LocalDate localDateStart = LocalDate.parse(startTime, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                enrollmentPlan.setStartTime(localDateStart);
                //String转localDate
                LocalDate localDateStop = LocalDate.parse(stopTime, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                enrollmentPlan.setStopTime(localDateStop);
                enrollmentPlan.setRegMode(reqMode);
                enrollmentPlan.setRegPlace(enpPlace);
                enrollmentPlan.setContact(contact);
                String phoneStr = Integer.toString(phone); //转Str
                enrollmentPlan.setPhone(phoneStr);
                enrollmentPlan.setEmail(email);
                enrollmentPlan.setRegistrationType(Collections.singletonList(registrationType));
                enrollmentPlan.setEnpItem(enpItem);
                enrollmentPlan.setGraduateRelatedMatters(graduateRelatedMatters);
                enrollmentPlan.setReplenishExplain(replenishExplain);

                enrollmentPlans.add(enrollmentPlan); //放入集合
            }
            enrollmentPlanPage.setRecords(enrollmentPlans);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return null;
    }
}


